# +----------------------------------------------------------------------
# | DjangoAdmin敏捷开发框架 [ 赋能开发者，助力企业发展 ]
# +----------------------------------------------------------------------
# | 版权所有 2021~2024 北京DjangoAdmin研发中心
# +----------------------------------------------------------------------
# | Licensed LGPL-3.0 DjangoAdmin并不是自由软件，未经许可禁止去掉相关版权
# +----------------------------------------------------------------------
# | 官方网站: https://www.djangoadmin.cn
# +----------------------------------------------------------------------
# | 作者: @一米阳光 团队荣誉出品
# +----------------------------------------------------------------------
# | 版权和免责声明:
# | 本团队对该软件框架产品拥有知识产权（包括但不限于商标权、专利权、著作权、商业秘密等）
# | 均受到相关法律法规的保护，任何个人、组织和单位不得在未经本团队书面授权的情况下对所授权
# | 软件框架产品本身申请相关的知识产权，禁止用于任何违法、侵害他人合法权益等恶意的行为，禁
# | 止用于任何违反我国法律法规的一切项目研发，任何个人、组织和单位用于项目研发而产生的任何
# | 意外、疏忽、合约毁坏、诽谤、版权或知识产权侵犯及其造成的损失 (包括但不限于直接、间接、
# | 附带或衍生的损失等)，本团队不承担任何法律责任，本软件框架禁止任何单位和个人、组织用于
# | 任何违法、侵害他人合法利益等恶意的行为，如有发现违规、违法的犯罪行为，本团队将无条件配
# | 合公安机关调查取证同时保留一切以法律手段起诉的权利，本软件框架只能用于公司和个人内部的
# | 法律所允许的合法合规的软件产品研发，详细声明内容请阅读《框架免责声明》附件；
# +----------------------------------------------------------------------

from datetime import datetime

from fastapi.logger import logger
from sqlalchemy import and_, text
from werkzeug.datastructures.structures import MultiDict

from apps.constants.message import PAGE_LIMIT
from apps.models.company import Company
from apps.models.goods_cost import GoodsCost
from apps.models.user import User
from apps.forms.company import CompanyForm
from extends import db
from utils import R, regular
from utils.utils import uid
from openpyxl import Workbook
from config.env import FASTAPI_ROOT_PATH, FASTAPI_IMAGE_URL
import pandas as pd
import io


# 查询公司分页数据
async def GoodsCostList(request):
    try:
        # 页码
        page = int(request.query_params.get("page", 1))
        # 每页数
        limit = int(request.query_params.get("limit", PAGE_LIMIT))
        # 实例化查询对象
        query = db.query(GoodsCost).filter(GoodsCost.is_delete == 0)
        # 用户账号
        title = request.query_params.get('title')
        if title:
            query = query.filter(GoodsCost.spec.like('%' + title + '%'))
        userID = uid(request)
        if userID != 1:  # 不是超级管理员
            user = db.query(User).filter(User.id == userID).first()
            query = query.filter(GoodsCost.company_id == user.company_id)
        # 排序
        query = query.order_by(GoodsCost.id.asc())
        # 记录总数
        count = query.count()
        # 分页查询
        list = query.limit(limit).offset((page - 1) * limit).all()
        # 实例化数组对象
        result = []
        # 遍历数据源
        if list:
            for item in list:
                # 对象转字典
                data = item.to_dict()
                # 加入列表
                result.append(data)
        # 返回结果
        return R.ok(data=result, count=count)
    except:
        # 抛出异常
        logger.error('运行异常')
        raise
    finally:
        # 关闭连接
        db.close()


# 查询分页数据
async def Export(request):
    try:
        # 页码
        page = int(request.query_params.get("page", 1))
        limit = 50000
        userID = uid(request)
        user = None
        params = {'limit': limit}
        sql = text(
            "select company_id,spec,out_sku_sn from fastapi_data_order group by out_sku_sn limit :limit")
        if userID != 1:
            user = db.query(User).filter(User.id == userID).first()
            params['company_id'] = user.company_id
            sql = text(
                "select company_id,spec,out_sku_sn from fastapi_data_order where company_id = :company_id group by out_sku_sn limit :limit")
        result = db.execute(sql, params).all()
        # 创建一个新的工作簿
        workbook = Workbook()
        # 选择默认的工作表
        active_sheet = workbook.active
        # 向工作表中添加数据
        active_sheet.title = "Sheet1"  # 修改工作表的名称
        if len(result) > 0:
            # 遍历数据源
            for key, item in enumerate(result):
                sheet_num = key + 1
                if sheet_num == 1:
                    active_sheet["A{0}".format(sheet_num)] = "规格编码"
                    active_sheet["B{0}".format(sheet_num)] = "规格"
                    active_sheet["C{0}".format(sheet_num)] = "成本价格"
                else:
                    active_sheet["A{0}".format(sheet_num)] = item[2]
                    active_sheet["B{0}".format(sheet_num)] = item[1]
                    active_sheet["C{0}".format(sheet_num)] = '{0}'.format(0.00)
        # 保存工作簿到文件
        file_name = "{0}_output_sku.xlsx".format(userID)
        save_path = "/uploads/{0}".format(file_name)
        workbook.save(FASTAPI_ROOT_PATH + save_path)
        return R.ok(data=FASTAPI_IMAGE_URL +"/"+ file_name)
    except:
        # 抛出异常
        logger.error('运行异常')
        raise
    finally:
        # 关闭连接
        db.close()


# 导入
async def Import(request, file):
    userID = uid(request)
    user = None
    if userID != 1:  # 不是超级管理员
        user = db.query(User).filter(User.id == userID).first()
    contents = await file.read()
    df = pd.read_excel(io.BytesIO(contents))
    for index, row in df.iterrows():
        try:
            row1 = row.get('规格编码')
            row2 = row.get('规格')
            row3 = row.get('成本价格')
            query = db.query(GoodsCost).filter(
                and_(GoodsCost.out_sku_sn == row1, GoodsCost.spec == row2, GoodsCost.is_delete == 0))
            if user:
                query = query.filter(GoodsCost.company_id == user.company_id)
            GoodsCostRow = query.first()
            if GoodsCostRow:  # 更新
                db.query(GoodsCost).filter_by(id=GoodsCostRow.id).update({'cost': row3})
                db.commit()
            else:
                addData = {
                    'id': '',
                    'uid': userID,
                    'company_id': '',
                    'out_sku_sn': row1,
                    'spec': row2,
                    'cost': row3
                }
                data = GoodsCost(**addData)
                if user:
                    data.company_id = user.company_id
                data.save()
        except:
            # 事务回滚
            db.rollback()
            raise
        finally:
            # 关闭连接
            db.close()

    return R.ok(msg='导入成功')
